﻿using System;
using System.Collections.Generic;
using System.Web;

using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;

using AppOne.data;

namespace AppOne.db
{
    public class DB
    {
        public static List<jlrd> Query()
        {
            List<jlrd> list = new List<jlrd>();
            SqlConnection conn = GetDatabaseConnection("");
            conn.Open();
            //查询语句
            string select = "SELECT * from jlrd t WHERE t.lg<=100";
            //构选命令
            SqlCommand cmd = new SqlCommand(select, conn);
            //执行查询
            SqlDataReader reader = cmd.ExecuteReader();
            jlrd cjlrd = null;
            while (reader.Read())
            {
                cjlrd = new jlrd();                
                cjlrd.Lg_numeric = (decimal)reader[0];
                cjlrd.Type_int = (int)reader[1];
                cjlrd.Id_int = (int)reader[2];
                cjlrd.Dt_date = (DateTime)reader[3];
                cjlrd.Tm_time = (DateTime)reader[4];
                cjlrd.Name_char = (string)reader[5];
                cjlrd.Tt_float = (double)reader[6];
                cjlrd.Pp_float = (double)reader[7];
                cjlrd.Fl_float = (double)reader[8];
                cjlrd.Dh_float = (double)reader[9];
                cjlrd.Mf_float = (double)reader[10];
                cjlrd.Mh_float = (double)reader[11];
                list.Add(cjlrd);
            }
            conn.Close();
            return list;
        }

        static private SqlConnection GetDatabaseConnection(string name = "conn_work_data")
        {
            //string strConn = ConfigurationManager.ConnectionStrings[name].ToString();
            //SqlConnection conn = new SqlConnection(strConn);
            //return conn;

            //string source = "server=(local);integrated security=SSPI;database=work";
            //string source = "server=localhost;database=work;uid=sa;pwd=hl2011";
            string source = ConfigurationManager.ConnectionStrings[name].ToString();
            SqlConnection conn = new SqlConnection(source);
            return conn;
        }

        public static List<string> QueryAllName()
        {
            List<string> list = new List<string>();
            SqlConnection conn = GetDatabaseConnection("conn_work_data");
            conn.Open();
            //查询语句
            string select = "SELECT DISTINCT(name) AS name from jlrd";
            //构选命令
            SqlCommand cmd = new SqlCommand(select, conn);
            //执行查询
            SqlDataReader reader = cmd.ExecuteReader();
            
            while (reader.Read())
            {
                list.Add(reader[0].ToString());
            }
            conn.Close();
            return list;
        }

        public static List<jlrd> QueryLastData()
        {
            List<jlrd> list = new List<jlrd>();
            SqlConnection conn = GetDatabaseConnection("conn_work_data");
            conn.Open();
            //查询语句
            string select = @"
                select TT1.name, TT1.dttm, TT2.tt, TT2.pp, TT2.fl, TT2.df, TT2.dh, TT2.mf, TT2.mh, TT2.dt, TT2.tm from 
                (
                    select name, max(convert(varchar(100), dt, 23) + ' ' + convert(varchar(100), tm, 24)) as dttm from jlrd group by name
                ) TT1,
                (
                    select convert(varchar(100), dt, 23) + ' ' + convert(varchar(100), tm, 24) as dttm
                    , lg, type, id, dt, tm, name, tt, pp, fl, df, dh, mf, mh
                    from jlrd
                ) TT2
                where TT1.name = TT2.name AND TT1.dttm = TT2.dttm";
            //构选命令
            SqlCommand cmd = new SqlCommand(select, conn);
            //执行查询
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                jlrd j = new jlrd();
                j.Name_char = (string)reader[0];
                //j.Dt_date = (DateTime)reader[1];
                j.Tt_float = (double)reader[2];
                j.Pp_float = (double)reader[3];
                j.Fl_float = (double)reader[4];
                j.Df_float = (double)reader[5];
                j.Dh_float = (double)reader[6];
                j.Mf_float = (double)reader[7];
                j.Mh_float = (double)reader[8];
                j.Dt_date = (DateTime)reader[9];
                j.Tm_time = (DateTime)reader[10];
                list.Add(j);
            }
            conn.Close();
            return list;
        }

        //datetimeStart : 开始时间
        //datetimeEnd : 结束时间
        //name : 名称
        //numOnePage : 第页多少条
        //page : 查询第几页
        public static List<jlrd> Query(string datetimeStart, string datetimeEnd, string name, int numOnePage, int page)
        {
            List<jlrd> list = new List<jlrd>();
            SqlConnection conn = GetDatabaseConnection();
            conn.Open();
            //拼接 查询语句
            string select = ""
                          + "SELECT lg, type, id, dt, tm, name, tt, pp, fl, df, dh, mf, mh "
                          + "FROM (	 "
                          + "    SELECT TOP "+numOnePage+" lg, type, id, dt, tm, name, tt, pp, fl, df, dh, mf, mh "
                          + "    FROM( "
                          + "        SELECT TOP "+numOnePage*page+" lg, type, id, dt, tm, name, tt, pp, fl, df, dh, mf, mh  "
                          + "        FROM( "
                          + "            SELECT	(convert(varchar(100),dt,23)+' '+convert(varchar(100),tm,24)) as dttm, * FROM jlrd "
                          + "        ) T  "
                          + "        WHERE 1=1 AND T.dttm<='"+datetimeEnd+"' AND T.dttm>='"+datetimeStart+"' AND name='"+name+"' "
                          + "        ORDER BY lg ASC "
                          + "    ) TEMPTABLE2 "
                          + "    ORDER BY TEMPTABLE2.lg DESC "
                          + ") TEMPTABLE3 "
                          + "ORDER BY TEMPTABLE3.lg ASC";


            //构选命令
            SqlCommand cmd = new SqlCommand(select, conn);
            //执行查询
            SqlDataReader reader = cmd.ExecuteReader();
            jlrd cjlrd = null;
            while (reader.Read())
            {
                cjlrd = new jlrd();
                cjlrd.Lg_numeric = (decimal)reader[0];
                cjlrd.Type_int = (int)reader[1];
                cjlrd.Id_int = (int)reader[2];
                cjlrd.Dt_date = (DateTime)reader[3];
                cjlrd.Tm_time = (DateTime)reader[4];
                cjlrd.Name_char = (string)reader[5];
                cjlrd.Tt_float = (double)reader[6];
                cjlrd.Pp_float = (double)reader[7];
                cjlrd.Fl_float = (double)reader[8];
                cjlrd.Dh_float = (double)reader[9];
                cjlrd.Mf_float = (double)reader[10];
                cjlrd.Mh_float = (double)reader[11];
                list.Add(cjlrd);
            }
            conn.Close();
            return list;
        }

        //查询总共有多少行
        public static int QueryTotalRow(string datetimeStart, string datetimeEnd, string name)
        {
            SqlConnection conn = GetDatabaseConnection("conn_work_data");
            conn.Open();
            //查询语句
            string select = @"
                    SELECT count(lg) nTotalCount
                    FROM(
                        SELECT	(convert(varchar(100),dt,23)+' '+convert(varchar(100),tm,24)) as dttm, * FROM jlrd
                    ) T 
                    WHERE 1=1 AND T.dttm<='"+datetimeEnd+"' AND T.dttm>='"+datetimeStart+"' AND name='"+name+"'";
            //构选命令
            SqlCommand cmd = new SqlCommand(select, conn);
            //执行查询
            SqlDataReader reader = cmd.ExecuteReader();
            int nTotalRow = 0;
            if(reader.Read())
            {
                nTotalRow = (int)reader[0];
            }
            conn.Close();
            return nTotalRow;
        }
    }
}